package application.parcelModule;

/**
 * Klasa zawiera metody potrzebne do twozenia zapytan Postgresql
 * 
 * Stwozyna analogicznie i na podstawie klasy ClientDatabase
 */

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

//import java.sql.DriverManager;

public class ParcelDatabase {

	String user;
	String password;

	Connection connection;

	Statement query;
	ResultSet result;

	Parcel parcel;
	Status status;
	static ArrayList<Parcel> parcelList;

	/*********************************************************************/
	/*************************** Constructors ***************************/
	/*******************************************************************/

	public ParcelDatabase(Connection connection) throws SQLException,
			ClassNotFoundException {
		super();
		ParcelDatabase.parcelList = new ArrayList<Parcel>();
		this.connection = connection;
		query = (Statement) connection.createStatement();
	}

	public ParcelDatabase(Connection connection, ArrayList<Parcel> list)
			throws SQLException, ClassNotFoundException {
		super();
		ParcelDatabase.parcelList = list;

		this.connection = connection;
		query = (Statement) connection.createStatement();
	}

	/*********************************************************************/
	/********************** Getters 'n' Setters *************************/
	/*******************************************************************/

	public String getUser() {
		return user;
	}

	public void setUser(String user) {
		this.user = user;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public Connection getConnection() {
		return connection;
	}

	public void setConnection(Connection connection) {
		this.connection = connection;
	}

	public Statement getQuery() {
		return query;
	}

	public void setQuery(Statement query) {
		this.query = query;
	}

	public ResultSet getResult() {
		return result;
	}

	public void setResult(ResultSet result) {
		this.result = result;
	}

	public Parcel getParcel() {
		return parcel;
	}

	public void setParcel(Parcel parcel) {
		this.parcel = parcel;
	}

	public static ArrayList<Parcel> getparcelList() {
		return ParcelDatabase.parcelList;
	}

	public void setParcelList(ArrayList<Parcel> parcelList) {
		this.parcelList = parcelList;
	}

	/*********************************************************************/
	/************************** Methods ***************************/
	/*******************************************************************/

	/**
	 * Zwraca informacje o wszystkich paczkach
	 */
	public ArrayList<Parcel> getAllParcel() throws SQLException {

		parcelList.clear();

		this.result = ((java.sql.Statement) query)
				.executeQuery("SELECT * FROM \"Parcel\"");

		if (result != null)
			while (result.next()) {
				try {

					parcel = new Parcel(Integer.parseInt(result
							.getString(1)),result.getString(2),
							result.getString(3), Integer.parseInt(result
									.getString(4)), Integer.parseInt(result
									.getString(5)), Integer.parseInt(result
									.getString(6)), Integer.parseInt(result
									.getString(7)), Integer.parseInt(result
									.getString(8)), Integer.parseInt(result
									.getString(9)), Integer.parseInt(result
									.getString(10)), Integer.parseInt(result
									.getString(11)));


					parcelList.add(parcel);

				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		return parcelList;
	}

	/**
	 * Zwraca informacje o wszystkich AKTYWNYCH paczkach “Wyświetlenie listy
	 * wszystkich aktywnych paczek wraz z dodatkowymi informacjami” Zakladamy ze
	 * aktywna paczka posiada status: 2, 'W przygotowaniu do wysylki' lub 3,
	 * 'Wyslana' lub 4, 'Dostarczona'
	 */
	public ArrayList<Parcel> getAllActivParcel() throws SQLException {

		parcelList.clear();

		this.result = ((java.sql.Statement) query)
				.executeQuery("SELECT * FROM \"Parcel\" WHERE \"status\" IN (2,3,4)");

		if (result != null)
			while (result.next()) {
				try {

					parcel = new Parcel(Integer.parseInt(result
							.getString(1)),result.getString(2),
							result.getString(3), Integer.parseInt(result
									.getString(4)), Integer.parseInt(result
									.getString(5)), Integer.parseInt(result
									.getString(6)), Integer.parseInt(result
									.getString(7)), Integer.parseInt(result
									.getString(8)), Integer.parseInt(result
									.getString(9)), Integer.parseInt(result
									.getString(10)), Integer.parseInt(result
									.getString(11)));


					parcelList.add(parcel);

				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		return parcelList;
	}

	/**
	 * ////////////////////////////////////////////////////////////////////////
	 * Pobieranie danych z bazy inzynier_ProjektIO wzgledem jednego z atrybutow
	 * ////////////////////////////////////////////////////////////////////////
	 */

	/**
	 * Zwraca informacje o Paczce o okreslonym id
	 */
	public Parcel getParcelById(int Id) throws SQLException {
		this.result = null;
		parcel = null;
		this.result = ((java.sql.Statement) query)
				.executeQuery("SELECT * FROM \"Parcel\" WHERE  id = "
						+ Id);

		if (result != null)
			while (result.next()) {
				try {

					parcel = new Parcel(Integer.parseInt(result
							.getString(1)),result.getString(2),
							result.getString(3), Integer.parseInt(result
									.getString(4)), Integer.parseInt(result
									.getString(5)), Integer.parseInt(result
									.getString(6)), Integer.parseInt(result
									.getString(7)), Integer.parseInt(result
									.getString(8)), Integer.parseInt(result
									.getString(9)), Integer.parseInt(result
									.getString(10)), Integer.parseInt(result
									.getString(11)));


				} catch (SQLException e) {
					// TODO Auto-generated catch block
					throw new NullPointerException("Brak paczki w systemie");
				}
			}
		return parcel;
	}

	/**
	 * Zwraca numer statusu (status z tabeli Parcel) Paczki o okreslonym id
	 */
	public int getStatusFromParcelById(int Id) throws SQLException {

		this.result = ((java.sql.Statement) query)
				.executeQuery("SELECT status FROM \"Parcel\" WHERE  \"Parcel\".\"id\" = "
						+ Id + " ");

		if (result != null) {
			while (result.next()) {
				try {
					int res = result.getInt(1);
					return res;

				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return -1;
				}
			}
		}
		return -1;

	}

	/**
	 * Zwraca informacje o Paczkach z okreslona data
	 */
	public ArrayList<Parcel> getParcelsByDate(String dateee)
			throws SQLException {

		parcelList.clear();

		this.result = ((java.sql.Statement) query)
				.executeQuery("SELECT * FROM \"Parcel\" WHERE  \"Parcel\".\"datee\" = \'"
						+ dateee + "\'");
		if (result != null)
			while (result.next()) {
				try {
					parcel = new Parcel(Integer.parseInt(result
							.getString(1)),result.getString(2),
							result.getString(3), Integer.parseInt(result
									.getString(4)), Integer.parseInt(result
									.getString(5)), Integer.parseInt(result
									.getString(6)), Integer.parseInt(result
									.getString(7)), Integer.parseInt(result
									.getString(8)), Integer.parseInt(result
									.getString(9)), Integer.parseInt(result
									.getString(10)), Integer.parseInt(result
									.getString(11)));


					parcelList.add(parcel);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		return parcelList;
	}

	/**
	 * Zwraca informacje o Paczkach ktore maja zadany typ
	 */
	public ArrayList<Parcel> getParcelsByType(String typeee)
			throws SQLException {

		parcelList.clear();

		this.result = ((java.sql.Statement) query)
				.executeQuery("SELECT * FROM \"Parcel\" WHERE  \"Parcel\".\"typee\" = \'"
						+ typeee + "\'");
		if (result != null)
			while (result.next()) {
				try {
					parcel = new Parcel(Integer.parseInt(result
							.getString(1)),result.getString(2),
							result.getString(3), Integer.parseInt(result
									.getString(4)), Integer.parseInt(result
									.getString(5)), Integer.parseInt(result
									.getString(6)), Integer.parseInt(result
									.getString(7)), Integer.parseInt(result
									.getString(8)), Integer.parseInt(result
									.getString(9)), Integer.parseInt(result
									.getString(10)), Integer.parseInt(result
									.getString(11)));


					parcelList.add(parcel);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		return parcelList;
	}

	/**
	 * Zwraca informacje o Paczkach ktora ma okreslony status
	 */
	public ArrayList<Parcel> getParcelsByStatus(int statuss)
			throws SQLException {

		parcelList.clear();

		this.result = ((java.sql.Statement) query)
				.executeQuery("SELECT * FROM \"Parcel\" WHERE  \"Parcel\".\"status\" = "
						+ statuss + " ");
		if (result != null)
			while (result.next()) {
				try {
					parcel = new Parcel(Integer.parseInt(result
							.getString(1)),result.getString(2),
							result.getString(3), Integer.parseInt(result
									.getString(4)), Integer.parseInt(result
									.getString(5)), Integer.parseInt(result
									.getString(6)), Integer.parseInt(result
									.getString(7)), Integer.parseInt(result
									.getString(8)), Integer.parseInt(result
									.getString(9)), Integer.parseInt(result
									.getString(10)), Integer.parseInt(result
									.getString(11)));

					parcelList.add(parcel);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		return parcelList;
	}

	/**
	 * Zwraca informacje o Paczkach ktore wyslal dany nadawca Zakladamy ze
	 * klientem jest tym co wysyla SENDER
	 */

	public ArrayList<Parcel> getParcelsByClient(int clientt)
			throws SQLException {

		parcelList.clear();

		this.result = ((java.sql.Statement) query)
				.executeQuery("SELECT * FROM \"Parcel\" WHERE  \"Parcel\".\"sender\" = "
						+ clientt + " ");

		if (result != null)
			while (result.next()) {
				try {
					parcel = new Parcel(Integer.parseInt(result
							.getString(1)),result.getString(2),
							result.getString(3), Integer.parseInt(result
									.getString(4)), Integer.parseInt(result
									.getString(5)), Integer.parseInt(result
									.getString(6)), Integer.parseInt(result
									.getString(7)), Integer.parseInt(result
									.getString(8)), Integer.parseInt(result
									.getString(9)), Integer.parseInt(result
									.getString(10)), Integer.parseInt(result
									.getString(11)));

					parcelList.add(parcel);

				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		return parcelList;
	}

	/**
	 * Zwraca informacje o Paczkach ktore ma otrzymac dany odbiorca
	 */
	public ArrayList<Parcel> getParcelsByRecipent(int recipientt)
			throws SQLException {

		parcelList.clear();

		this.result = ((java.sql.Statement) query)
				.executeQuery("SELECT * FROM \"Parcel\" WHERE  \"Parcel\".\"recipient\" = "
						+ recipientt + " ");
		if (result != null)
			while (result.next()) {
				try {
					parcel = new Parcel(Integer.parseInt(result
							.getString(1)),result.getString(2),
							result.getString(3), Integer.parseInt(result
									.getString(4)), Integer.parseInt(result
									.getString(5)), Integer.parseInt(result
									.getString(6)), Integer.parseInt(result
									.getString(7)), Integer.parseInt(result
									.getString(8)), Integer.parseInt(result
									.getString(9)), Integer.parseInt(result
									.getString(10)), Integer.parseInt(result
									.getString(11)));


					parcelList.add(parcel);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		return parcelList;
	}

	/**
	 * Zwraca informacje o Paczkach o podanej cenie przesylki
	 */
	public ArrayList<Parcel> getParcelsByPrice(int pricee) throws SQLException {

		parcelList.clear();

		this.result = ((java.sql.Statement) query)
				.executeQuery("SELECT * FROM \"Parcel\" WHERE  \"Parcel\".\"price\" = "
						+ pricee + " ");
		if (result != null)
			while (result.next()) {
				try {
					parcel = new Parcel(Integer.parseInt(result
							.getString(1)),result.getString(2),
							result.getString(3), Integer.parseInt(result
									.getString(4)), Integer.parseInt(result
									.getString(5)), Integer.parseInt(result
									.getString(6)), Integer.parseInt(result
									.getString(7)), Integer.parseInt(result
									.getString(8)), Integer.parseInt(result
									.getString(9)), Integer.parseInt(result
									.getString(10)), Integer.parseInt(result
									.getString(11)));


					parcelList.add(parcel);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		return parcelList;
	}

	/**
	 * Zwarac nazwe statusu po podaniu id statusu
	 */
	public String getStatus(int id_statusu) throws SQLException {

		this.result = ((java.sql.Statement) query)
				.executeQuery("SELECT * FROM \"Status\" WHERE  \"Status\".\"id\" = "
						+ id_statusu + " ");
		if (result != null)
			while (result.next()) {
				try {
					status = new Status(Integer.parseInt(result.getString(1)),
							result.getString(2));
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		return status.name;
	}
	
	/**
	 * Zwarac nazwe statusu po podaniu id statusu
	 */
	public ArrayList<String> getStatusList() throws SQLException {
		ArrayList<String> arr = new ArrayList<String>();
		this.result = ((java.sql.Statement) query)
				.executeQuery("SELECT * FROM \"Status\"");
		if (result != null)
			while (result.next()) {
				try {
					status = new Status(Integer.parseInt(result.getString(1)),
							result.getString(2));
					arr.add(status.name);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		return arr;
	}

	/**
	 * Dodawanie Paczki zwraca id paczki (ktora teraz dodal)
	 */
	public boolean addParcels() throws SQLException {

		for (Parcel p : parcelList) {
			this.query
					.execute("INSERT INTO \"Parcel\"(id, datee, typee, status, sender, recipient, width ,height,length ,mass , price)"
							+ " VALUES ("
							+ parcel.getParcelID()
							+ ", \'"
							+ parcel.getDate()
							+ "\',\'"
							+ parcel.getType()
							+ "\', "
							+ 0
							+ ", "
							+ parcel.getSender()
							+ ","
							+ parcel.getRecipient()
							+ ", "
							+ parcel.getWidth()
							+ ", "
							+ parcel.getHeight()
							+ ", "
							+ parcel.getLength()
							+ ", "
							+ parcel.getMass()
							+ ", "
							+ parcel.getPrice() + ")");
			this.result = this.query.getResultSet();
		}
		return true;
	}

	/**
	 * Dodawanie Paczki
	 */
	public int addParcel(Parcel parcel) throws SQLException {

		this.parcel = parcel;
		int currId = getCurrentID();
		this.query
				.execute("INSERT INTO \"Parcel\"(id, datee, typee, status, sender, recipient, width ,height,length ,mass , price)"
						+ " VALUES ("
						+ currId
						+ ", \'"
						+ parcel.getDate()
						+ "\',\'"
						+ parcel.getType()
						+ "\', "
						+ 1
						+ ", "
						+ parcel.getSender()
						+ ","
						+ parcel.getRecipient()
						+ ", "
						+ parcel.getWidth()
						+ ", "
						+ parcel.getHeight()
						+ ", "
						+ parcel.getLength()
						+ ", "
						+ parcel.getMass()
						+ ", " + parcel.getPrice() + ")");

		this.result = this.query.getResultSet();

		return currId;
	}
	
	/**
	 * Pobiera akltualnie najwyzszy numer id z bazy danych 
	 * z tabeli parcel oraz zwraca go powiekszonego o 1 dzieki 
	 * czemu przy puzniejszym zapisie do bazy mamy unikalne id
	 */
	public int getCurrentID() throws SQLException{

		int tmpID = 0;

		this.result = ((java.sql.Statement) query).executeQuery( 
				" SELECT max(id) FROM \"Parcel\" ");

		if (result != null)  
			while(result.next())  
			{ 
				try {
					tmpID = this.result.getInt(1);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} 
			}

		return (tmpID+1);
	}


	/**
	 * Usuwa pojedyncza paczke z tabelei dla konkretnego id
	 */
	public boolean deleteParcel(int id) throws SQLException {

		this.query.execute("DELETE FROM \"Parcel\" WHERE id = " + id + "");
		this.result = this.query.getResultSet();

		return true;
	}

	/**
	 * Modyfikuje tabele Parcel
	 */
	public boolean modifyParcel(Parcel p) throws SQLException {
		int a;
		a = getStatusFromParcelById(p.getParcelID());
		/**
		 * Modyfikuje tabele tylko gdy paczka ma status 1, 'Nieaktywna' lub "2,
		 * 'W przygotowaniu do wysylki', dla wiekszego statusu zwraca false
		 */
		if (a < 3) {
			String sql = "UPDATE \"Parcel\" SET recipient=? WHERE id = "
					+ p.getParcelID() + "";
			PreparedStatement prest = connection.prepareStatement(sql);
			prest.setInt(1, p.getRecipient());

			prest.executeUpdate();

			return true;
		} else {
			return false;
		}

	}
	
	/**
	 * Modyfikuje status Paczki
	 */
	public boolean modifyParcelStatus(int parcelID, int status) throws SQLException {
		
		/**
		 * Modyfikuje tabele tylko gdy paczka ma status 1, 'Nieaktywna' lub "2,
		 * 'W przygotowaniu do wysylki', dla wiekszego statusu zwraca false
		 */
		
			String sql = "UPDATE \"Parcel\" SET status=? WHERE id = "
					+ parcelID + "";
			PreparedStatement prest = connection.prepareStatement(sql);
			prest.setInt(1, status);

			prest.executeUpdate();

			return true;

	}

	/**
	 * Tworzy tabele Parcel
	 */
	public void createTable() throws SQLException {

		this.query
				.execute("CREATE TABLE \"Parcel\""
						+ "("
						+ " id integer NOT NULL,"
						+ " datee character varying(30) NOT NULL,"
						+ " typee character varying(50) NOT NULL,"
						+ " status integer NOT NULL,"
						+ " sender integer NOT NULL,"
						+ " recipient integer NOT NULL,"
						+ " price integer NOT NULL,"
						+ " CONSTRAINT \"Parcel_pkey\" PRIMARY KEY (id),"
						+ "CONSTRAINT \"Recipient_Clients_fkey\" FOREIGN KEY (recipient)"
						+ " REFERENCES \"Clients\" (id) MATCH FULL"
						+ " ON UPDATE NO ACTION ON DELETE CASCADE,"
						+ "CONSTRAINT \"Sender_Clients_fkey\" FOREIGN KEY (sender)"
						+ " REFERENCES \"Clients\" (id) MATCH FULL"
						+ " ON UPDATE NO ACTION ON DELETE CASCADE,"
						+ "CONSTRAINT \"Status_status_fkey\" FOREIGN KEY (status)"
						+ " REFERENCES \"Status\" (id) MATCH FULL"
						+ " ON UPDATE NO ACTION ON DELETE CASCADE,"
						+ "CONSTRAINT id_check CHECK (id > 0)," + ")"
						+ "WITH (OIDS=FALSE);");
		this.result = this.query.getResultSet();

	}

	/**
	 * Metoda usuwajaca tabele parcel
	 */
	public void dropTable() throws SQLException {

		this.query.execute("DROP TABLE \"Parcel\"");
		this.result = this.query.getResultSet();

	}

}
